package com.yuanyu.mapper.mysqlMapper;

import com.yuanyu.model.entity.MainTable;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface StaffMapper {
    /*
    * 子表1     initial_maintenance_inspection_report
    * 子表2     repair_and_disassembly_report
    * 子表3     pump_maintenance_and_disassembly_report
    * 子表4     serious_damage_and_quality_assessment
    * 子表5     assembly_record_of_dry_pump
    * 子表6     multistage_dry_pump_assembly_records
    * 子表7     hoisting_pump
    * 子表8     dry_pump_unit_assembly_record
    * 子表9     dry_pump_unit_electrical_control_system_inspection_record
    * 子表10    factory_test_report
    * */

    //1
    @Select("select COUNT(*) from main_table m where production_processes_1 = 1 and state = 0 AND NOT EXISTS (SELECT 1 FROM initial_maintenance_inspection_report s WHERE s.input_Case = m.Case)")
    Integer selPageTable1();

    @Select("select * from main_table m where production_processes_1 = 1 and state = 0 AND NOT EXISTS (SELECT 1 FROM initial_maintenance_inspection_report s WHERE s.input_Case = m.Case  ) ORDER BY urgent DESC ,start_time DESC LIMIT #{number},10")
    List<MainTable> queryPaginatedData1(Integer number);

    //2
    @Select("select COUNT(*) from main_table m where production_processes_2 = 1 and state = 0 AND NOT EXISTS (SELECT 1 FROM repair_and_disassembly_report s WHERE s.input_Case = m.Case)")
    Integer selPageTable2();

    @Select("select * from main_table m where production_processes_2 = 1 and state = 0 AND NOT EXISTS (SELECT 1 FROM repair_and_disassembly_report s WHERE s.input_Case = m.Case) ORDER BY urgent DESC ,start_time DESC LIMIT #{number},10")
    List<MainTable> queryPaginatedData2(Integer number);

    //3
    @Select("select COUNT(*) from main_table m where production_processes_3 = 1 and state = 0 " +
            "AND NOT EXISTS (SELECT 1 FROM pump_maintenance_and_disassembly_report s WHERE s.input_Case = m.Case)")
    Integer selPageTable3();

    @Select("select * from main_table m where production_processes_3 = 1 and state = 0 " +
            "AND NOT EXISTS (SELECT 1 FROM pump_maintenance_and_disassembly_report s WHERE s.input_Case = m.Case) ORDER BY urgent DESC ,start_time DESC LIMIT #{number},10")
    List<MainTable> queryPaginatedData3(Integer number);

    //4
    @Select("select COUNT(*) from main_table m where production_processes_4 = 1 and state = 0 " +
            "AND NOT EXISTS (SELECT 1 FROM serious_damage_and_quality_assessment s WHERE s.input_Case = m.Case)")
    Integer selPageTable4();

    @Select("select * from main_table m where production_processes_4 = 1 and state = 0 " +
            "AND NOT EXISTS (SELECT 1 FROM serious_damage_and_quality_assessment s WHERE s.input_Case = m.Case) ORDER BY urgent DESC ,start_time DESC LIMIT #{number},10")
    List<MainTable> queryPaginatedData4(Integer number);

    //5
    @Select("select COUNT(*) from main_table m where production_processes_5 = 1 and state = 0 " +
            "AND NOT EXISTS (SELECT 1 FROM assembly_record_of_dry_pump s WHERE s.input_Case = m.Case)")
    Integer selPageTable5();

    @Select("select * from main_table m where production_processes_5 = 1 and state = 0 " +
            "AND NOT EXISTS (SELECT 1 FROM assembly_record_of_dry_pump s WHERE s.input_Case = m.Case) ORDER BY urgent DESC ,start_time DESC LIMIT #{number},10")
    List<MainTable> queryPaginatedData5(Integer number);

    //6
    @Select("select COUNT(*) from main_table m where production_processes_6 = 1 and state = 0 " +
            "AND NOT EXISTS (SELECT 1 FROM multistage_dry_pump_assembly_records s WHERE s.input_Case = m.Case)")
    Integer selPageTable6();

    @Select("select * from main_table m where production_processes_6 = 1 and state = 0 " +
            "AND NOT EXISTS (SELECT 1 FROM multistage_dry_pump_assembly_records s WHERE s.input_Case = m.Case) ORDER BY urgent DESC ,start_time DESC LIMIT #{number},10")
    List<MainTable> queryPaginatedData6(Integer number);

    //7
    @Select("select COUNT(*) from main_table m where production_processes_7 = 1 and state = 0 " +
            "AND NOT EXISTS (SELECT 1 FROM hoisting_pump s WHERE s.input_Case = m.Case)")
    Integer selPageTable7();

    @Select("select * from main_table m where production_processes_7 = 1 and state = 0 " +
            "AND NOT EXISTS (SELECT 1 FROM hoisting_pump s WHERE s.input_Case = m.Case) ORDER BY urgent DESC ,start_time DESC LIMIT #{number},10")
    List<MainTable> queryPaginatedData7(Integer number);

    //8
    @Select("select COUNT(*) from main_table m where production_processes_8 = 1 and state = 0 " +
            "AND NOT EXISTS (SELECT 1 FROM dry_pump_unit_assembly_record s WHERE s.input_Case = m.Case)")
    Integer selPageTable8();

    @Select("select * from main_table m where production_processes_8 = 1 and state = 0 " +
            "AND NOT EXISTS (SELECT 1 FROM dry_pump_unit_assembly_record s WHERE s.input_Case = m.Case) ORDER BY urgent DESC ,start_time DESC LIMIT #{number},10")
    List<MainTable> queryPaginatedData8(Integer number);

    //9
    @Select("select COUNT(*) from main_table m where production_processes_9 = 1 and state = 0 " +
            "AND NOT EXISTS (SELECT 1 FROM dry_pump_unit_electrical_control_system_inspection_record s WHERE s.input_Case = m.Case)")
    Integer selPageTable9();

    @Select("select * from main_table m where production_processes_9 = 1 and state = 0 " +
            "AND NOT EXISTS (SELECT 1 FROM dry_pump_unit_electrical_control_system_inspection_record s WHERE s.input_Case = m.Case) ORDER BY urgent DESC ,start_time DESC LIMIT #{number},10")
    List<MainTable> queryPaginatedData9(Integer number);

    //10
    @Select("select COUNT(*) from main_table m where production_processes_10 = 1 and state = 0 " +
            "AND NOT EXISTS (SELECT 1 FROM factory_test_report s WHERE s.input_Case = m.Case)")
    Integer selPageTable10();

    @Select("select * from main_table m where production_processes_10 = 1 and state = 0 " +
            "AND NOT EXISTS (SELECT 1 FROM factory_test_report s WHERE s.input_Case = m.Case) ORDER BY urgent DESC ,start_time DESC LIMIT #{number},10")
    List<MainTable> queryPaginatedData10(Integer number);

    //Case查工单
    @Select("select * from main_table m where m.Case = #{Case}")
    MainTable queryByCase(String Case);


    //查询1-9需要完成的工序是否完成  (在主表值为1的就是需要完成的)
    @Select("SELECT * FROM main_table m " +
            "WHERE m.Case = #{Case}" +
            "  AND m.state = 0" +
            "  AND (" +
            "    production_processes_1 = 1 AND NOT EXISTS (SELECT 1 FROM initial_maintenance_inspection_report s WHERE s.input_Case = m.Case)" +
            "    OR production_processes_2 = 1 AND NOT EXISTS (SELECT 1 FROM repair_and_disassembly_report s WHERE s.input_Case = m.Case)" +
            "    OR production_processes_3 = 1 AND NOT EXISTS (SELECT 1 FROM pump_maintenance_and_disassembly_report s WHERE s.input_Case = m.Case)" +
            "    OR production_processes_4 = 1 AND NOT EXISTS (SELECT 1 FROM serious_damage_and_quality_assessment s WHERE s.input_Case = m.Case)" +
            "    OR production_processes_5 = 1 AND NOT EXISTS (SELECT 1 FROM assembly_record_of_dry_pump s WHERE s.input_Case = m.Case)" +
            "    OR production_processes_6 = 1 AND NOT EXISTS (SELECT 1 FROM multistage_dry_pump_assembly_records s WHERE s.input_Case = m.Case)" +
            "    OR production_processes_7 = 1 AND NOT EXISTS (SELECT 1 FROM hoisting_pump s WHERE s.input_Case = m.Case)" +
            "    OR production_processes_8 = 1 AND NOT EXISTS (SELECT 1 FROM dry_pump_unit_assembly_record s WHERE s.input_Case = m.Case)" +
            "    OR production_processes_9 = 1 AND NOT EXISTS (SELECT 1 FROM dry_pump_unit_electrical_control_system_inspection_record s WHERE s.input_Case = m.Case)" +
            "  )")
    MainTable queryByCaseId(String Case);

    //查询1-10需要完成的工序是否完成  (在主表值为1的就是需要完成的)
    @Select("SELECT * FROM main_table m " +
            "WHERE m.Case = #{Case}" +
            "  AND m.state = 1")
    MainTable queryByCaseIdAll(String Case);
}
